﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace ZG_member.DAL
{
	/// <summary>
	/// 数据访问类:wasteBook
	/// </summary>
	public partial class wasteBook
	{
		public wasteBook()
		{}
		#region  Method

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("id", "wasteBook"); 
		}



        public DataSet GetSQL(string sql)
        {
            return DbHelperSQL.Query(sql);
        }






		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from wasteBook");
            strSql.Append(" where details=@details");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
};
			parameters[0].Value = id;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}
//        public bool Exists(int id)
//        {
//            StringBuilder strSql = new StringBuilder();
//            strSql.Append("select count(1) from wasteBook");
//            strSql.Append(" where details=@details");
//            SqlParameter[] parameters = {
//                    new SqlParameter("@id", SqlDbType.Int,4)
//};
//            parameters[0].Value = id;

//            return DbHelperSQL.Exists(strSql.ToString(), parameters);
//        }
        public string keyongjifen(string memberId, int day)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select SUM(consumeIntegral) from dbo.wasteBook");
            strSql.Append(" where memberCardId = @memberCardId  and DateDiff(DAY,GETDATE(),DateAdd(YEAR,DateDiff(YEAR,date,GETDATE()),date))> -@day and DateDiff(DAY,GETDATE(),DateAdd(YEAR,DateDiff(YEAR,date,GETDATE()),date))<=0 and savingMoney =0");
            SqlParameter[] parameters = {
					new SqlParameter("@memberCardId", SqlDbType.NVarChar,50),
                    new SqlParameter("@day", SqlDbType.Int,4)};
            parameters[0].Value = memberId;
            parameters[1].Value = day;
            string aa = "0";
            try
            {
               aa = DbHelperSQL.GetSingle(strSql.ToString(), parameters).ToString();
            }
            catch (Exception)
            {

                aa = "0";
                return aa;
            }
            return aa;
        }

		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(ZG_member.Model.wasteBook model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into wasteBook(");
            strSql.Append("terminalId,operatorId,memberCardId,date,consumeType,consumeTimes,consumeMoney,cardPayMoney,cashPayMoney,cardBalance,refundmentMoney,refundmentIntegral,exchangeIntegral,consumeIntegral,totalIntegral,savingMoney,details,Discount,jiedairen,Checkout,Voucher,VoucherMoney,ChangepricePeople )");
			strSql.Append(" values (");
            strSql.Append("@terminalId,@operatorId,@memberCardId,@date,@consumeType,@consumeTimes,@consumeMoney,@cardPayMoney,@cashPayMoney,@cardBalance,@refundmentMoney,@refundmentIntegral,@exchangeIntegral,@consumeIntegral,@totalIntegral,@savingMoney,@details,@Discount,@jiedairen,@Checkout,@Voucher,@VoucherMoney,@ChangepricePeople )");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@terminalId", SqlDbType.NVarChar,50),
					new SqlParameter("@operatorId", SqlDbType.NVarChar,50),
					new SqlParameter("@memberCardId", SqlDbType.NVarChar,50),
					new SqlParameter("@date", SqlDbType.Date,3),
					new SqlParameter("@consumeType", SqlDbType.Int,4),
					new SqlParameter("@consumeTimes", SqlDbType.NVarChar,50),
					new SqlParameter("@consumeMoney", SqlDbType.Money,8),
					new SqlParameter("@cardPayMoney", SqlDbType.Money,8),
					new SqlParameter("@cashPayMoney", SqlDbType.Money,8),
					new SqlParameter("@cardBalance", SqlDbType.Money,8),
					new SqlParameter("@refundmentMoney", SqlDbType.Money,8),
					new SqlParameter("@refundmentIntegral", SqlDbType.Int,4),
					new SqlParameter("@exchangeIntegral", SqlDbType.Int,4),
					new SqlParameter("@consumeIntegral", SqlDbType.Int,4),
					new SqlParameter("@totalIntegral", SqlDbType.Int,4),
					new SqlParameter("@savingMoney", SqlDbType.Money,8),
					new SqlParameter("@details", SqlDbType.NVarChar,1024),
                    new SqlParameter("@Discount",SqlDbType.Float,10),
                    new SqlParameter("@jiedairen",SqlDbType.NVarChar,50),
                    new SqlParameter("@Checkout",SqlDbType.VarChar),
                    new SqlParameter("@Voucher",SqlDbType.VarChar),
                    new SqlParameter("@VoucherMoney",SqlDbType.Money),
                    new SqlParameter("@ChangepricePeople",SqlDbType.VarChar)};
			parameters[0].Value = model.terminalId;
			parameters[1].Value = model.operatorId;
			parameters[2].Value = model.memberCardId;
			parameters[3].Value = model.date;
			parameters[4].Value = model.consumeType;
			parameters[5].Value = model.consumeTimes;
			parameters[6].Value = model.consumeMoney;
			parameters[7].Value = model.cardPayMoney;
			parameters[8].Value = model.cashPayMoney;
			parameters[9].Value = model.cardBalance;
			parameters[10].Value = model.refundmentMoney;
			parameters[11].Value = model.refundmentIntegral;
			parameters[12].Value = model.exchangeIntegral;
			parameters[13].Value = model.consumeIntegral;
			parameters[14].Value = model.totalIntegral;
			parameters[15].Value = model.savingMoney;
			parameters[16].Value = model.details;
            parameters[17].Value = model.Discount;
            parameters[18].Value = model.Jiedairen;
            parameters[19].Value = model.Checkout;
            parameters[20].Value = model.Voucher;
            parameters[21].Value = model.VoucherMoney;
            parameters[22].Value = model.ChangepricePeople;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(ZG_member.Model.wasteBook model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update wasteBook set ");
			strSql.Append("terminalId=@terminalId,");
			strSql.Append("operatorId=@operatorId,");
			strSql.Append("memberCardId=@memberCardId,");
			strSql.Append("date=@date,");
			strSql.Append("consumeType=@consumeType,");
			strSql.Append("consumeTimes=@consumeTimes,");
			strSql.Append("consumeMoney=@consumeMoney,");
			strSql.Append("cardPayMoney=@cardPayMoney,");
			strSql.Append("cashPayMoney=@cashPayMoney,");
			strSql.Append("cardBalance=@cardBalance,");
			strSql.Append("refundmentMoney=@refundmentMoney,");
			strSql.Append("refundmentIntegral=@refundmentIntegral,");
			strSql.Append("exchangeIntegral=@exchangeIntegral,");
			strSql.Append("consumeIntegral=@consumeIntegral,");
			strSql.Append("totalIntegral=@totalIntegral,");
			strSql.Append("savingMoney=@savingMoney,");
			strSql.Append("details=@details,");
            strSql.Append("Discount=@Discount,");
            strSql.Append("jiedairen=@jiedairen,");
            strSql.Append("Checkout=@Checkout,");
            strSql.Append("Voucher=@Voucher,");
            strSql.Append("VoucherMoney=@VoucherMoney,");
            strSql.Append("ChangepricePeople=@ChangepricePeople");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
					new SqlParameter("@terminalId", SqlDbType.NVarChar,50),
					new SqlParameter("@operatorId", SqlDbType.NVarChar,50),
					new SqlParameter("@memberCardId", SqlDbType.NVarChar,50),
					new SqlParameter("@date", SqlDbType.Date,3),
					new SqlParameter("@consumeType", SqlDbType.Int,4),
					new SqlParameter("@consumeTimes", SqlDbType.NVarChar,50),
					new SqlParameter("@consumeMoney", SqlDbType.Money,8),
					new SqlParameter("@cardPayMoney", SqlDbType.Money,8),
					new SqlParameter("@cashPayMoney", SqlDbType.Money,8),
					new SqlParameter("@cardBalance", SqlDbType.Money,8),
					new SqlParameter("@refundmentMoney", SqlDbType.Money,8),
					new SqlParameter("@refundmentIntegral", SqlDbType.Int,4),
					new SqlParameter("@exchangeIntegral", SqlDbType.Int,4),
					new SqlParameter("@consumeIntegral", SqlDbType.Int,4),
					new SqlParameter("@totalIntegral", SqlDbType.Int,4),
					new SqlParameter("@savingMoney", SqlDbType.Money,8),
					new SqlParameter("@details", SqlDbType.NVarChar,1024),
                    new SqlParameter("@Discount",SqlDbType.Float,10),
                    new SqlParameter("@jiedairen",SqlDbType.NVarChar,50),
                    new SqlParameter("@Checkout",SqlDbType.VarChar),
                    new SqlParameter("@Voucher",SqlDbType.VarChar),
                    new SqlParameter("@VoucherMoney",SqlDbType.Money),
                    new SqlParameter("@ChangepricePeople",SqlDbType.VarChar),
					new SqlParameter("@id", SqlDbType.Int,4)};
			parameters[0].Value = model.terminalId;
			parameters[1].Value = model.operatorId;
			parameters[2].Value = model.memberCardId;
			parameters[3].Value = model.date;
			parameters[4].Value = model.consumeType;
			parameters[5].Value = model.consumeTimes;
			parameters[6].Value = model.consumeMoney;
			parameters[7].Value = model.cardPayMoney;
			parameters[8].Value = model.cashPayMoney;
			parameters[9].Value = model.cardBalance;
			parameters[10].Value = model.refundmentMoney;
			parameters[11].Value = model.refundmentIntegral;
			parameters[12].Value = model.exchangeIntegral;
			parameters[13].Value = model.consumeIntegral;
			parameters[14].Value = model.totalIntegral;
			parameters[15].Value = model.savingMoney;
			parameters[16].Value = model.details;
            parameters[17].Value = model.Discount;
            parameters[18].Value = model.Jiedairen;
            parameters[19].Value = model.Checkout;
            parameters[20].Value = model.Voucher;
            parameters[21].Value = model.VoucherMoney;
            parameters[22].Value = model.ChangepricePeople;
			parameters[23].Value = model.id;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}





		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from wasteBook ");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
};
			parameters[0].Value = id;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string idlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from wasteBook ");
			strSql.Append(" where id in ("+idlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
        public bool DeleteListbyCardid(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from wasteBook ");
            strSql.Append(" where membercardid = (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public ZG_member.Model.wasteBook GetModel(int id)
		{
			
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select  top 1 id,terminalId,operatorId,memberCardId,date,consumeType,consumeTimes,consumeMoney,cardPayMoney,cashPayMoney,cardBalance,refundmentMoney,refundmentIntegral,exchangeIntegral,consumeIntegral,totalIntegral,savingMoney,details,Discount,jiedairen,Checkout,Voucher,VoucherMoney,ChangepricePeople  from wasteBook ");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
};
			parameters[0].Value = id;

			ZG_member.Model.wasteBook model=new ZG_member.Model.wasteBook();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["id"]!=null && ds.Tables[0].Rows[0]["id"].ToString()!="")
				{
					model.id=int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
				}
				if(ds.Tables[0].Rows[0]["terminalId"]!=null && ds.Tables[0].Rows[0]["terminalId"].ToString()!="")
				{
					model.terminalId=ds.Tables[0].Rows[0]["terminalId"].ToString();
				}
				if(ds.Tables[0].Rows[0]["operatorId"]!=null && ds.Tables[0].Rows[0]["operatorId"].ToString()!="")
				{
					model.operatorId=ds.Tables[0].Rows[0]["operatorId"].ToString();
				}
				if(ds.Tables[0].Rows[0]["memberCardId"]!=null && ds.Tables[0].Rows[0]["memberCardId"].ToString()!="")
				{
					model.memberCardId=ds.Tables[0].Rows[0]["memberCardId"].ToString();
				}
				if(ds.Tables[0].Rows[0]["date"]!=null && ds.Tables[0].Rows[0]["date"].ToString()!="")
				{
					model.date=DateTime.Parse(ds.Tables[0].Rows[0]["date"].ToString());
				}
				if(ds.Tables[0].Rows[0]["consumeType"]!=null && ds.Tables[0].Rows[0]["consumeType"].ToString()!="")
				{
					model.consumeType=int.Parse(ds.Tables[0].Rows[0]["consumeType"].ToString());
				}
				if(ds.Tables[0].Rows[0]["consumeTimes"]!=null && ds.Tables[0].Rows[0]["consumeTimes"].ToString()!="")
				{
					model.consumeTimes=ds.Tables[0].Rows[0]["consumeTimes"].ToString();
				}
				if(ds.Tables[0].Rows[0]["consumeMoney"]!=null && ds.Tables[0].Rows[0]["consumeMoney"].ToString()!="")
				{
					model.consumeMoney=decimal.Parse(ds.Tables[0].Rows[0]["consumeMoney"].ToString());
				}
				if(ds.Tables[0].Rows[0]["cardPayMoney"]!=null && ds.Tables[0].Rows[0]["cardPayMoney"].ToString()!="")
				{
					model.cardPayMoney=decimal.Parse(ds.Tables[0].Rows[0]["cardPayMoney"].ToString());
				}
				if(ds.Tables[0].Rows[0]["cashPayMoney"]!=null && ds.Tables[0].Rows[0]["cashPayMoney"].ToString()!="")
				{
					model.cashPayMoney=decimal.Parse(ds.Tables[0].Rows[0]["cashPayMoney"].ToString());
				}
				if(ds.Tables[0].Rows[0]["cardBalance"]!=null && ds.Tables[0].Rows[0]["cardBalance"].ToString()!="")
				{
					model.cardBalance=decimal.Parse(ds.Tables[0].Rows[0]["cardBalance"].ToString());
				}
				if(ds.Tables[0].Rows[0]["refundmentMoney"]!=null && ds.Tables[0].Rows[0]["refundmentMoney"].ToString()!="")
				{
					model.refundmentMoney=decimal.Parse(ds.Tables[0].Rows[0]["refundmentMoney"].ToString());
				}
				if(ds.Tables[0].Rows[0]["refundmentIntegral"]!=null && ds.Tables[0].Rows[0]["refundmentIntegral"].ToString()!="")
				{
					model.refundmentIntegral=int.Parse(ds.Tables[0].Rows[0]["refundmentIntegral"].ToString());
				}
				if(ds.Tables[0].Rows[0]["exchangeIntegral"]!=null && ds.Tables[0].Rows[0]["exchangeIntegral"].ToString()!="")
				{
					model.exchangeIntegral=int.Parse(ds.Tables[0].Rows[0]["exchangeIntegral"].ToString());
				}
				if(ds.Tables[0].Rows[0]["consumeIntegral"]!=null && ds.Tables[0].Rows[0]["consumeIntegral"].ToString()!="")
				{
					model.consumeIntegral=int.Parse(ds.Tables[0].Rows[0]["consumeIntegral"].ToString());
				}
				if(ds.Tables[0].Rows[0]["totalIntegral"]!=null && ds.Tables[0].Rows[0]["totalIntegral"].ToString()!="")
				{
					model.totalIntegral=int.Parse(ds.Tables[0].Rows[0]["totalIntegral"].ToString());
				}
				if(ds.Tables[0].Rows[0]["savingMoney"]!=null && ds.Tables[0].Rows[0]["savingMoney"].ToString()!="")
				{
					model.savingMoney=decimal.Parse(ds.Tables[0].Rows[0]["savingMoney"].ToString());
				}
				if(ds.Tables[0].Rows[0]["details"]!=null && ds.Tables[0].Rows[0]["details"].ToString()!="")
				{
					model.details=ds.Tables[0].Rows[0]["details"].ToString();
				}
                if (ds.Tables[0].Rows[0]["Discount"] != null && ds.Tables[0].Rows[0]["Discount"].ToString() != "")
                {
                    model.Discount =Convert.ToSingle(ds.Tables[0].Rows[0]["Discount"]);
                }
                if (ds.Tables[0].Rows[0]["jiedairen"] != null && ds.Tables[0].Rows[0]["jiedairen"].ToString() != "")
                {
                    model.Jiedairen = ds.Tables[0].Rows[0]["jiedairen"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Checkout"] != null && ds.Tables[0].Rows[0]["Checkout"].ToString() != "")
                {
                    model.Checkout = ds.Tables[0].Rows[0]["Checkout"].ToString(); ;
                }
                if (ds.Tables[0].Rows[0]["Voucher"] != null && ds.Tables[0].Rows[0]["Voucher"].ToString() != "")
                {
                    model.Voucher = ds.Tables[0].Rows[0]["Voucher"].ToString(); ;
                }
                if (ds.Tables[0].Rows[0]["VoucherMoney"] != null && ds.Tables[0].Rows[0]["VoucherMoney"].ToString() != "")
                {
                    model.VoucherMoney =Convert.ToDouble(ds.Tables[0].Rows[0]["VoucherMoney"]);
                }
                if (ds.Tables[0].Rows[0]["ChangepricePeople"] != null && ds.Tables[0].Rows[0]["ChangepricePeople"].ToString() != "")
                {
                    model.ChangepricePeople = ds.Tables[0].Rows[0]["ChangepricePeople"].ToString();
                }
				return model;
			}
			else
			{
				return null;
			}
		}

        public bool Updatepoint(string memberid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update wasteBook set ");
            strSql.Append("consumeIntegral= 0 where memberCardId = @memberCardId");
            SqlParameter[] parameters = {					
                                        new SqlParameter("@memberCardId", SqlDbType.NVarChar,200)};
            parameters[0].Value = memberid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool Updatedetails(string memberid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update wasteBook set ");
            strSql.Append("details= 1 where id = @id");
            SqlParameter[] parameters = {					
                                        new SqlParameter("@memberCardId", SqlDbType.NVarChar,200)};
            parameters[0].Value = memberid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public DataSet Pagination(int pageSize, int pageNum,string strWhere)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select top("+pageSize+") * from wasteBook ");
            sb.Append("where id not in(select top((" + pageNum + "-1)*" + pageSize + ") id from wasteBook ");
            if (strWhere.Trim() != "")
            {
                sb.Append("where " + strWhere + " order by id ) and "+strWhere+"");
            }
            else
            {
                sb.Append("order by id)");
            }
            sb.Append(" order by id");
            return DbHelperSQL.Query(sb.ToString());
        }


		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select id,terminalId,operatorId,memberCardId,date,consumeType,consumeTimes,consumeMoney,cardPayMoney,cashPayMoney,cardBalance,refundmentMoney,refundmentIntegral,exchangeIntegral,consumeIntegral,totalIntegral,savingMoney,details,Discount,jiedairen,Checkout,Voucher,VoucherMoney,ChangepricePeople");
			strSql.Append(" FROM wasteBook ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
            strSql.Append(" id,terminalId,operatorId,memberCardId,date,consumeType,consumeTimes,consumeMoney,cardPayMoney,cashPayMoney,cardBalance,refundmentMoney,refundmentIntegral,exchangeIntegral,consumeIntegral,totalIntegral,savingMoney,details,Discount,jiedairen,Checkout,Voucher,VoucherMoney,ChangepricePeople ");
			strSql.Append(" FROM wasteBook ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "wasteBook";
			parameters[1].Value = "id";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  Method
	}
}

